﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EarthGrid.sqlite
{
    public class SqliteTool
    {
        private SQLiteConnection connection;
        private SQLiteConnection searchConnection;
        private SQLiteCommand command;
        private SQLiteCommand searchCommand;
        private SQLiteTransaction transaction;

        private int transactionCount = 0;

        //批量提交
        private int transactionLimit = 1000;

        private static readonly string createMetadataTableString = "CREATE TABLE metadata (name text, value text);";
        /// <summary>
        /// 球面网格数据表
        /// </summary>
        private static readonly string 建球面网格表 = "CREATE TABLE grid_2d (g_level int, g_code VARCHAR(32), min_lon NUMERIC, min_lat NUMERIC, max_lon NUMERIC, max_lat NUMERIC);";
        
        /// <summary>
        /// 记录各级别每个层的划分信息，包括级别，层号，高度编码，层自身高度，距地面高度，距地心高度，底边长度，弧度，这些都是可以通过计算获得
        /// </summary>
        private static readonly string 高度划分信息表 = "CREATE TABLE grid_3d (g_level int, n_index INTEGER, n_code VARCHAR(32), h_self NUMERIC, h_dimian NUMERIC, r_dixin NUMERIC, r_hudu NUMERIC, l_len NUMERIC);";
        private static readonly string index1String = "CREATE UNIQUE INDEX grid_index1 on grid_2d (g_code);";

        private static readonly string insertMetadataString = "INSERT INTO metadata (name, value) VALUES (@name, @value);";
        private static readonly string 插入球面网格 = "INSERT INTO grid_2d (g_level, g_code, min_lon, min_lat, max_lon, max_lat) VALUES (@g_level, @g_code, @min_lon, @min_lat, @max_lon, @max_lat);";
        // private static readonly string 插入立体网格 = "";

        /// <summary>
        /// 文件名全路径
        /// </summary>
        public string FilePath { get; set; }

        /// <summary>
        /// 创建sqlite数据库
        /// </summary>
        /// <param name="filePath">数据库文件</param>
        public SqliteTool(String filePath)
        {
            FilePath = filePath;
            SQLiteConnection.CreateFile(filePath);
            SQLiteConnectionStringBuilder sqLiteConnectionStringBuilder = new SQLiteConnectionStringBuilder
            {
                Version = 3,
                Pooling = true,
                FailIfMissing = true,
                DataSource = filePath
            };
            String connectionString = sqLiteConnectionStringBuilder.ConnectionString;
            connection = new SQLiteConnection(connectionString);
            searchConnection = new SQLiteConnection(connectionString);
            //SQLiteConnection.ConnectionPool.
            //sqLiteConnectionStringBuilder.
            command = GetCommand();
            command.CommandText = createMetadataTableString;
            command.ExecuteNonQuery();

            command.CommandText = 建球面网格表;
            command.ExecuteNonQuery();

            /*command.CommandText = 高度划分信息表;
            command.ExecuteNonQuery();*/

        }

        /// <summary>
        /// 
        /// </summary>
        public void BeginTransaction()
        {
            SQLiteTransaction sQLiteTransaction = connection.BeginTransaction();
            GetCommand().Transaction = sQLiteTransaction;
            transactionCount = 0;
        }

        /// <summary>
        /// 
        /// </summary>
        public void TransactionCommit()
        {
            SQLiteTransaction transaction = GetCommand().Transaction;
            if (null == transaction)
            {
                return;
            }
            transaction.Commit();
            transactionCount = 0;
        }

        public void SaveGrid(int level, string code, double minLon, double minLat, double maxLon, double maxLat)
        {
            SQLiteCommand command = GetCommand();
            lock (command)
            {
                SQLiteParameter[] commandParameters = new SQLiteParameter[6]
                {
                    CreateParameter("g_level", System.Data.DbType.Int16, level),
                    CreateParameter("g_code", System.Data.DbType.String, code),
                    CreateParameter("min_lon", System.Data.DbType.Double, minLon),
                    CreateParameter("min_lat", System.Data.DbType.Double, minLat),
                    CreateParameter("max_lon", System.Data.DbType.Double, maxLon),
                    CreateParameter("max_lat", System.Data.DbType.Double, maxLat)
                };
                command.Parameters.AddRange(commandParameters);
                command.CommandText = 插入球面网格;
                int result = command.ExecuteNonQuery();
                if (++transactionCount >= transactionLimit)
                {
                    transaction = command.Transaction;
                    if (null != transaction && null != transaction.Connection)
                        transaction.Commit();
                    transactionCount = 0;
                    transaction = command.Connection.BeginTransaction();
                    command.Transaction = transaction;
                }
            }
        }

        private SQLiteCommand GetCommand()
        {
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            if (null == command)
                command = new SQLiteCommand(connection);

            return command;
        }

        private SQLiteCommand GetSearchCommand()
        {
            if (searchConnection.State != System.Data.ConnectionState.Open)
            {
                searchConnection.Open();
            }

            if (searchCommand == null)
                searchCommand = new SQLiteCommand(searchConnection);

            return searchCommand;
        }

        private SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
        {
            SQLiteParameter parameter = new SQLiteParameter();
            parameter.DbType = parameterType;
            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;
            return parameter;
        }


    }
}
